# # coding=utf-8
import openpyxl
import xlrd
import time

paramerter = {'平方分米':'032平方米',
 '毫米':'030米',
 '件':'011',
 '千克':'035',
 '米':'030',
 '平方米':'032',
 '瓶':'142',
 '卷':'018',
 '个':'007',
 '英尺':'067',
 '加仑':'097英加仑098美加仑',
 '"2':'002',
 '码':'086',
 '毫升':'096',
 '夸脱':'097英加仑098美加仑',
 '升':'095',
 '盎司':'083',
 '磅':'076',
 '品脱':'097英加仑098美加仑',
 '平方英尺':'111',
 '套':'006',
 '副':'019',
 '批':'121',
 '英寸':'088',
 'Q/T':'001',
 'B/P':'001',
 '罐':'122',
 '盒':'140',
 '立方毫米':'033立方米',
 '立方米':'033',
 '吨':'070',
 '分米':'030米',
 '箱':'120'}

ma_pars = {'料件':'I'}

excel_path = ('../../项目文件\库存.xlsx', '../../项目文件\库存.xlsx' )

class Excel_repalce():
    def check(self, file_path):
        start_time = time.strftime('%Y%m%d%H%M%S')

        data = xlrd.open_workbook(file_path)
        table = data.sheet_by_index(0)

        address = table.cell(7, 10).value
        orgid = "104320"

        # 定义一个新的excel
        error_data = openpyxl.Workbook()
        error_sheet = error_data.create_sheet(index=0)
        error_sheet.cell(1, 1, str("企业期初库存#"))
        error_sheet.cell(2, 1, str("企业十位数编码"))
        error_sheet.cell(2, 2, str("企业名称"))
        error_sheet.cell(2, 3, str("企业信用代码"))
        error_sheet.cell(2, 4, str("统计日期"))
        error_sheet.cell(2, 5, str("组织id"))
        error_sheet.cell(3, 1, str("3122260WGC"))
        error_sheet.cell(3, 2, str("上海飞机制造有限公司"))
        error_sheet.cell(3, 3, str("91310000132612172J"))
        error_sheet.cell(3, 4, str(time.strftime('%Y%m%d%H%M%S')))
        if address != '大场':
            orgid = '103200'
        error_sheet.cell(3, 5, str(orgid))
        error_sheet.cell(4, 1, str("企业期初库存#明细"))
        error_sheet.cell(5, 1, str("料号"))
        error_sheet.cell(5, 2, str("商品名称"))
        error_sheet.cell(5, 3, str("商品编码"))
        error_sheet.cell(5, 4, str("计量单位"))
        error_sheet.cell(5, 5, str("料件成品标志"))
        error_sheet.cell(5, 6, str("数量"))
        error_sheet.cell(5, 7, str("总价"))
        error_sheet.cell(5, 8, str("币制"))
        error_sheet.cell(5, 9, str("仓库号"))
        error_sheet.cell(5, 10, str("库位号"))
        error_sheet.cell(5, 11, str("原数据对应行"))

        unit_count = []
        s = 0

        # 查看工作表
        # table = ""
        data.sheet_names()
        print("全部sheet页如下：\n" + str(data.sheet_names()))

        print("总行数：" + str(table.nrows))
        print("总列数：" + str(table.ncols))

        for i in range(5, table.nrows):
            """
            print("料号：%s" % gm1)
            print("商品名称：%s" % name)
            print("商品编码：%s" % gc)
            print("单位：%s" % dcl_unit)
            print("料件成品标志：%s" % ma)
            print("数量：%s" % number)
            print("总价：%s" % total)
            print("币值：%s" % bz)
            print("仓库号：%s" % ck)
            print("仓位号：%s" % kw)
            """
            # print("-----------------------sheet页:%s-第%s行---------------------：" % (s, str(i)))
            gm1 = str.strip(table.cell(i, 0).value)
            name = str.strip(table.cell(i, 1).value)
            gc = str.strip(str(table.cell(i, 2).value))
            dcl_unit = str.strip(table.cell(i, 3).value)
            if dcl_unit not in unit_count:
                unit_count.append(dcl_unit)
            ma = str.strip(table.cell(i, 4).value)
            number = table.cell(i, 5).value
            total = table.cell(i, 6).value
            bz = str.strip(table.cell(i, 7).value)
            ck = str.strip(table.cell(i, 8).value)
            kw = str.strip(table.cell(i, 9).value)

            error_sheet.cell(s + 6, 1, str("%s" % gm1))
            error_sheet.cell(s + 6, 2, str("%s" % name))
            error_sheet.cell(s + 6, 3, str("%s" % gc))
            if dcl_unit in paramerter.keys():
                error_sheet.cell(s + 6, 4, str(paramerter.get(dcl_unit)[0:3]))
            else:
                print('单位：%s' % dcl_unit)
                error_sheet.cell(s + 6, 4, str("%s" % dcl_unit))
            if ma in ma_pars.keys():
                error_sheet.cell(s + 6, 5, str(ma_pars.get(ma)))
            else:
                print('料件成品标志：%s' % ma)
                error_sheet.cell(s + 6, 5, str("%s" % ma))
            error_sheet.cell(s + 6, 6, str("%s" % number))
            error_sheet.cell(s + 6, 7, str("%s" % total))
            error_sheet.cell(s + 6, 8, str("%s" % bz))
            error_sheet.cell(s + 6, 9, str("%s" % ck))
            error_sheet.cell(s + 6, 10, str("%s" % kw))
            error_sheet.cell(s + 6, 11, str("%s" % (i+1)))
            s += 1

        print("******************3*******************%s" % s)

        error_data.save("../../项目文件\===%s修正后期初%s.xlsx" % (address, time.strftime('%Y%m%d%H%M%S')))

        end_time = time.strftime('%Y%m%d%H%M%S')

        print(unit_count)
        print("开始时间：" + str(start_time))
        print("结束时间：" + str(end_time))


for l in excel_path:
    Excel_repalce().check(file_path=l)